﻿Imports System.Configuration
Imports System.Data.OleDb

Public Class ProductoInsumo
    Public connDB As OleDb.OleDbConnection
    Dim cmd As OleDb.OleDbCommand

    Private Sub ComboBox2_SelectionChangeCommitted(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectionChangeCommitted
        listar_combo2()
        listar()
    End Sub

    Private Sub ProductoInsumo_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        listar_combo()
    End Sub


    Public Sub conexion()
        Try
            Dim conString As String = ConfigurationManager.ConnectionStrings("conDB").ConnectionString
            connDB = New OleDb.OleDbConnection(conString)
            'MsgBox("conextado corextamente")
        Catch ea As Exception
            MsgBox(ea.Message)
            connDB.Close()
        End Try
    End Sub

    Public Sub listar_combo()
        conexion()
        Dim MiDataSet As New DataSet
        Dim MiAdaptador As New OleDb.OleDbDataAdapter
        Try
            connDB.Open()
            Dim sql As String = "SELECT Id_Producto, Producto FROM Productos "

            Dim comando As New OleDbCommand(sql, connDB)
            MiAdaptador.SelectCommand = comando
            MiAdaptador.Fill(MiDataSet)

            ComboBox2.DataSource = MiDataSet.Tables(0)
            ComboBox2.DisplayMember = MiDataSet.Tables(0).Columns(1).Caption.ToString()
            ComboBox2.ValueMember = MiDataSet.Tables(0).Columns(0).Caption.ToString()
            ComboBox2.SelectedIndex = -1
        Catch ex As Exception
            MessageBox.Show("error " & Err.Number.ToString & vbCrLf & ex.Message)
            connDB.Close()
        End Try
    End Sub

    Public Sub listar_combo2()
        conexion()
        Dim MiDataSet As New DataSet
        Dim MiAdaptador As New OleDb.OleDbDataAdapter
        Try
            connDB.Open()
            Dim sql As String = "SELECT Id_insumo, nombre_insumo FROM Insumos "

            Dim comando As New OleDbCommand(sql, connDB)
            MiAdaptador.SelectCommand = comando
            MiAdaptador.Fill(MiDataSet)

            ComboBox1.DataSource = MiDataSet.Tables(0)
            ComboBox1.DisplayMember = MiDataSet.Tables(0).Columns(1).Caption.ToString()
            ComboBox1.ValueMember = MiDataSet.Tables(0).Columns(0).Caption.ToString()
        Catch ex As Exception
            MessageBox.Show("error " & Err.Number.ToString & vbCrLf & ex.Message)
            connDB.Close()
        End Try
    End Sub

    Public Sub listar()
        conexion()
        Dim MiDataSet As New DataSet
        Dim MiAdaptador As New OleDb.OleDbDataAdapter
        Try
            connDB.Open()
            Dim sql As String = "SELECT p.ID_insumo as [Id Insumo], i.Nombre_insumo as [Insumo], p.Cantidad FROM Prod_insumo p, Insumos i where p.id_insumo = i.Id_insumo and p.id_producto=" & ComboBox2.SelectedValue

            Dim comando As New OleDbCommand(sql, connDB)
            MiAdaptador.SelectCommand = comando
            MiAdaptador.Fill(MiDataSet)
            DataGridView1.DataSource = MiDataSet.Tables(0)

        Catch ex As Exception
            MessageBox.Show("error " & Err.Number.ToString & vbCrLf & ex.Message)
            connDB.Close()
        End Try
    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim Id_tipo As Integer = ComboBox1.SelectedValue
        Try
            conexion()
            connDB.Open()
            cmd = New OleDb.OleDbCommand("INSERT INTO Prod_insumo(Id_insumo, id_producto, cantidad) values (" & ComboBox1.SelectedValue & ", " & ComboBox2.SelectedValue & ", '" & TextBox1.Text & "')", connDB)
            cmd.ExecuteNonQuery()
            connDB.Close()
            MsgBox("Producto Registrado Exitosamente")
            listar()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim Id_tipo As Integer = ComboBox1.SelectedValue
        Try
            conexion()
            connDB.Open()
            cmd = New OleDb.OleDbCommand("UPDATE Prod_insumo SET cantidad = " & TextBox1.Text & " WHERE Id_producto = " & ComboBox1.SelectedValue & " and Id_insumo = " & ComboBox2.SelectedValue, connDB)
            cmd.ExecuteNonQuery()
            connDB.Close()
            MsgBox("La Actualización del Producto fue Exitosa")
            listar()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Try
            conexion()
            connDB.Open()
            cmd = New OleDb.OleDbCommand("DELETE FROM Prod_insumo WHERE Id_producto = " & ComboBox2.SelectedValue & " and Id_insumo = " & ComboBox1.SelectedValue, connDB)
            cmd.ExecuteNonQuery()
            connDB.Close()
            MsgBox("El Producto fue Eliminado Exitosamente")
            listar()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub DataGridView1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.SelectionChanged
        TextBox1.Text = Me.DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(2).Value
        ComboBox1.SelectedValue = Me.DataGridView1.Rows(DataGridView1.CurrentRow.Index).Cells(0).Value
    End Sub

End Class